﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _009_SqlLite
{
    class Program
    {
        static void Main(string[] args)
        {
            //创建数据库介质文件
            SQLiteHelper.CreateDb();
            //获取连接
            SQLiteHelper.GetCondition();

            //创建表
            string tableName = "UserInfo";
            if (SQLiteHelper.SelectSql($"select * from sqlite_master where name = '{tableName}'").Rows.Count == 0)
            {
                string tableCreateSql = $@"CREATE TABLE {tableName}(
	                            ID Integer primary key autoincrement NOT NULL,
	                            UserName varchar(128) NOT NULL,
	                            Password varchar(128) NULL,
	                            Message varchar(2048) NULL)";
                SQLiteHelper.DealSql(tableCreateSql);
            }
            //表清空
            string sql = $"delete from {tableName}";
            SQLiteHelper.DealSql(sql);
            //sql = $"update "

            //查询
            sql = $"Select * from {tableName}";
            DataTable dt = SQLiteHelper.SelectSql(sql);

            //插入
            sql = $@"INSERT INTO {tableName} (UserName, Password, Message) VALUES ('001', '001', '123')";
            SQLiteHelper.DealSql(sql);
            sql = $@"INSERT INTO {tableName} (UserName, Password, Message) VALUES ('002', '002', '456')";
            SQLiteHelper.DealSql(sql);
            sql = $@"INSERT INTO {tableName} (UserName, Password, Message) VALUES ('003', '003', '789')";
            SQLiteHelper.DealSql(sql);

            //查询
            sql = $"Select * from {tableName}";
            dt = SQLiteHelper.SelectSql(sql);

            //显示
            foreach (DataRow row in dt.Rows)
            {
                string space = "    ";
                foreach (DataColumn column in dt.Columns)
                {
                    Console.Write(row[column.ColumnName].ToString() + space);
                }
                Console.WriteLine();
            }

            Console.ReadKey();
        }
    }
}
